/*
#大写
SHOW DATABASES ;#查看数据库

CREATE DATABASE mydb;   #创建数据库
CREATE DATABASE mydb1 CHARSET = utf8;
CREATE DATABASE mydb2 CHARSET = GBK;

SHOW CREATE DATABASE mydb ; #查看数据库创建语句
SHOW CREATE DATABASE mydb1 ;
SHOW CREATE DATABASE mydb2 ;


DROP DATABASE mydb; #删除数据库
DROP DATABASE mydb1;
DROP DATABASE mydb2;

USE mydb;   #选择数据库
USE mydb1;
USE mydb2;

CREATE TABLE user(  #创建表
                     id INT,                 #id字段，类型为整型
                     username VARCHAR(32),   #username字段，类型为字符串，长度32（可以存32个字符）
                     password VARCHAR(32),
                     nickname VARCHAR(32),
                     age INT(3)              #age字段，类型为整型，长度3，最多存3为整数
);

DESC user;  #查看表结构
DESC userinfo;

SHOW CREATE TABLE user; #查看表创建的信息

SHOW TABLES ;   #查看所有表

RENAME TABLE user TO userinfo;  #修改表名

DROP TABLE userinfo;    #删除表

CREATE TABLE hero(
    name VARCHAR(32),
    AGE INT
);

ALTER TABLE hero ADD gender VARCHAR(10);    #修改表结构
DESC hero;
ALTER TABLE hero ADD id INT FIRST;  #在表的最开始添加字段
ALTER TABLE hero ADD pwd VARCHAR(32) AFTER name;    #在指定位置添加字段
ALTER TABLE hero DROP pwd;  #删除字段
ALTER TABLE hero CHANGE age age INT(5); #修改表字段
ALTER TABLE hero CHANGE age age VARCHAR(20);
ALTER TABLE hero CHANGE gender nickname VARCHAR(30) NOT NULL;

CREATE DATABASE mydb4 CHARSET = utf8;
SHOW DATABASES;
USE mydb4;
CREATE TABLE teacher(
    name VARCHAR(32)
);
DESC teacher;
DESC t;
SHOW TABLES;
ALTER TABLE teacher ADD age INT(3);
ALTER TABLE teacher ADD id INT FIRST;
ALTER TABLE teacher ADD salary INT AFTER name;
ALTER TABLE teacher DROP age;
RENAME TABLE teacher TO t;
DROP TABLE t;
DROP DATABASE mydb4;

USE mydb;
CREATE TABLE person(
    name VARCHAR(30),
    age INT(3)
);
SHOW TABLES;
DESC hero;
DESC person;
INSERT INTO person (name,age) VALUES('张三',33);  #插入数据
INSERT INTO person (age,name) VALUES (18,'李四');
INSERT INTO person (name,age) VALUES (20,'王五'); #错误
INSERT INTO person (age,name) VALUES (18);       #错误
INSERT INTO person (name) VALUES ('王五');
SELECT * FROM person;   #*表示所有列,查看表中所有记录

CREATE TABLE myperson(
    name VARCHAR(30) DEFAULT '无名氏',
    age INT DEFAULT 18
);
DESC myperson;
SELECT * FROM myperson;
DESC person;
ALTER TABLE person CHANGE name name VARCHAR(30) DEFAULT '无名氏';
INSERT INTO myperson (name) VALUES ('张三');
INSERT INTO myperson (age) VALUES (30);
INSERT INTO myperson VALUES ('李四',50);
INSERT INTO myperson VALUES ('李四');     #错误
INSERT INTO myperson VALUES (50,'李四');  #错误
INSERT INTO myperson VALUES (DEFAULT,99);
INSERT INTO myperson VALUES (NULL,DEFAULT);
INSERT INTO myperson(name, age) VALUES ('老大',1),('老二',2),('老三',3);

UPDATE person
SET age = 40;
SELECT * FROM person;

UPDATE person
SET age = 22
WHERE name='张三';

UPDATE person
SET age=36
WHERE age=40;

UPDATE person
SET age=39
WHERE age>30;
SELECT * FROM person;

UPDATE person
SET name='李老四',age=66
WHERE name='李四';
SHOW TABLES;

DELETE FROM person
WHERE name='李老四';

DELETE FROM person
WHERE age<30;
SELECT * FROM person;
INSERT INTO person VALUES (DEFAULT,99);
INSERT INTO person VALUES (NULL,DEFAULT);
INSERT INTO person(name, age) VALUES ('老大',1),('老二',2),('老三',3);

SHOW DATABASES ;
CREATE DATABASE day2db CHARSET = utf8;
SHOW CREATE DATABASE day2db;
USE day2db;
CREATE TABLE t_hero(
    name VARCHAR(30)
);
DESC t_hero;
RENAME TABLE t_hero TO hero;
DESC hero;
ALTER TABLE hero ADD money INT(6);
ALTER TABLE hero ADD id INT FIRST;
ALTER TABLE hero ADD age INT(3) AFTER name;
INSERT INTO hero(id,name,age,money)
VALUES (1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888);
SELECT *FROM hero;
UPDATE hero
SET age=52
WHERE name='刘备';

UPDATE hero
SET money=5000
WHERE age<=50;

DELETE FROM hero
WHERE money=5000;

DROP TABLE hero;
SHOW TABLES;
DROP DATABASE day2db;
SHOW DATABASES;
SHOW TABLES;    #失败
CREATE TABLE hero(
    id INT
);  #失败，No database selected
USE mydb;
CREATE TABLE person1(
    id INT,
    age INT UNSIGNED
);
SHOW TABLES;
DESC person1;
INSERT INTO person1(id) VALUES (1.1);
INSERT INTO person1(id) VALUES (4.4);
INSERT INTO person1(id) VALUES (4.6);#四舍五入
INSERT INTO person1(age) VALUES (-1);#失败
SELECT * FROM person1;
SHOW TABLES;

CREATE TABLE person2(
    age INT(3), #最大999
    salary DOUBLE(7,2)  #最大99999.99
);
DESC person2;
SELECT * FROM person2;
DELETE FROM person2
WHERE salary=9999.99;
INSERT INTO person2(salary) VALUES (99999.99);
INSERT INTO person2(salary) VALUES (999999);    #错误
INSERT INTO person2(salary) VALUES (1234.567);  #1234.57四舍五入
INSERT INTO person2(salary) VALUES (99999.994);
INSERT INTO person2(salary) VALUES (99999.996); #错误
SELECT * FROM person2;
SHOW TABLES;
CREATE TABLE userinfo(
    id INT,
    name VARCHAR(30),
    gender CHAR(1),
    birth DATETIME,
    salary DOUBLE(7,2)
);
DESC userinfo;
SELECT * FROM userinfo;
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (1,'张三','男','1992-12-24 23:12:50',123.456);
DELETE FROM userinfo
WHERE id=1;
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (2,'李四','女','1992-12-24',2500.25);
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (1,'张三','男','23:12:50',123.456); #错误
SHOW TABLES;
CREATE TABLE user1(
    id INT PRIMARY KEY ,
    name VARCHAR(30),
    age INT(3)
);
DESC user1;
SELECT * FROM user1;
INSERT INTO user1(id,name,age)VALUES (1,'张三',22),(2,'李四',33);
INSERT INTO user1(id,name,age)VALUES (1,'张三',22);#错误，重复主键
INSERT INTO user1(id,name,age)VALUES (NULL,'李四',33);#错误，主键不能为空
INSERT INTO user1(name,age)VALUES ('李四',33);#错误，主键不能为空

UPDATE user1
SET id=1
WHERE name='李四';#错误，id=1已经有了

UPDATE user1
SET id=NULL
WHERE name='张三';#错误，主键不能为空

CREATE TABLE user2(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    age INT(3)
);
DESC user2;
DESC user1;
SELECT * FROM user2;
ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT;
INSERT INTO user2 (name, age) VALUES ('张三',22);
INSERT INTO user2 (name, age) VALUES ('李四',34);
INSERT INTO user2 (id,name, age) VALUES (NULL,'王五',44);

CREATE TABLE user3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    age INT(3) NOT NULL
);
DESC user3;
DESC user2;
ALTER TABLE user2 CHANGE name name VARCHAR(30) NOT NULL ;
INSERT INTO user3 (name, age) VALUES (NULL,22);#错误
INSERT INTO user3 (age) VALUES (22);#错误
SELECT * FROM user3;
INSERT INTO user3 (name, age) VALUES ('张三',22);
INSERT INTO user3 (name, age) VALUES ('张三',33);

UPDATE user3
SET name=NULL
WHERE name='张三';#错误

CREATE TABLE user4(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) UNIQUE,
    age INT(3)
);
DESC user4;
SELECT * FROM user4;

ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE;#，不能有重复值，默认可以为空
ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE NOT NULL ;
DESC user3;
SELECT * FROM user3;
UPDATE user3
SET name='李四'
WHERE id=2;

DESC user4;
SELECT * FROM user4;
INSERT INTO user4 (name, age) VALUES ('张三',22);
INSERT INTO user4 (name, age) VALUES ('李四',33);
INSERT INTO user4 (name, age) VALUES ('李四',55);#错误
INSERT INTO user4 (name, age) VALUES (NULL,55);
INSERT INTO user4 (name, age) VALUES (NULL,66);
INSERT INTO user4 (id,name, age) VALUES (6,NULL,66);
DELETE FROM user4
WHERE id=6;

CREATE TABLE user5(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    age  INT(3) CHECK ( age > 0 AND age < 120 )
);
DESC user5;
SELECT * FROM user5;
INSERT INTO user5 (name, age) VALUES ('张三',20);
INSERT INTO user5 (name, age) VALUES ('李四',0);
INSERT INTO user5 (name, age) VALUES ('李四',150);

UPDATE user5
SET age=150
WHERE id=1;


USE tedu;
SELECT * FROM teacher;
SELECT name,salary,gender,title
FROM teacher;

SELECT name,age,gender,birth
FROM student;

SELECT name,age,title,salary
FROM teacher
WHERE title='一级讲师';

SELECT name,salary,comm,title
FROM teacher
WHERE name<>'刘苍松';

SELECT name,title,salary,comm,age
FROM teacher
WHERE age>=30;

SELECT name,age,gender
FRom student
WHERE job='大队长';

SELECT name,floor
FROM class
WHERE floor>=2;

SELECT name,age,gender,job
FROM student
WHERE age=7 AND job='大队长';

SELECT name,title,salary
FROM teacher
WHERE title='一级讲师' OR title='三级讲师';

SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<6 AND job='中队长';

SELECT name,gender,age,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长';

#AND优先级高于OR，因此可以用（）
SELECT name,age,gender,job,class_id
FROM student
WHERE class_id<=6 AND (job='大队长' OR job='中队长');

SELECT name,gender,age,job
FROM student
#WHERE job='大队长' OR job='中队长' OR job='小队长';
WHERE job IN('大队长','中队长','小队长');#这两句等价

SELECT name,title,salary,gender
FROM teacher
WHERE title IN('一级讲师','二级讲师','三级讲师');

SELECT name,title,salary
FROM teacher
#WHERE title<>'一级讲师' AND title<>'二级讲师';
WHERE title NOT IN('一级讲师','二级讲师');#这两句等价

SELECT name,gender,age,salary
FROM teacher
#WHERE salary>=2000 AND salary<=5000;
WHERE salary BETWEEN 2000 AND 5000;#两句等价

SELECT name,gender,age
FROM student
WHERE age BETWEEN 7 AND 10;

SELECT name,gender,age,title
FROM teacher
WHERE age BETWEEN 20 AND 35 AND gender='男';

SELECT DISTINCT title
FROM teacher;

SELECT DISTINCT job
FROM student;

SELECT DISTINCT age,job
FROM student;

SELECT name,gender,subject_id
FROM teacher
WHERE subject_id=1 AND gender='男';

SELECT name,gender,salary
FROM teacher
WHERE salary>5000 AND gender='女';

SELECT name,gender,salary
FROM teacher
WHERE salary>5000 AND gender='男'
OR gender='女';

SELECT name,age,job
FROM student
WHERE age=9 AND job IN('学习委员','语文课代表');

SELECT name,salary
FROM teacher
WHERE salary BETWEEN 6000 AND 10000;
6.查看工资在4000到8000以外的老师及具体工资?
SELECT name,salary
FROM teacher
WHERE salary NOT BETWEEN 4000 AND 8000;

SELECT DISTINCT subject_id
FROM teacher;

SELECT DISTINCT title
FROM teacher
WHERE gender='女';

SELECT DISTINCT job
FROM student
WHERE age BETWEEN 7 AND 10
  AND gender='男';

SELECT name,comm,title
FROM teacher
WHERE title IN('一级讲师','二级讲师');

SELECT name,salary,comm
FROM teacher
WHERE title NOT IN('老板','总监');

SELECT name,floor
FROM class
WHERE name IN('3年级2班','5年级3班');


SELECT name,age,gender,salary
FROM teacher
WHERE name LIKE '%晶%';

SELECT name,age,gender
FROM student
WHERE name LIKE '张%';

SELECT name,gender,age
FROM student
WHERE name LIKE '_平_';

SELECT name,age,gender
FROM teacher
WHERE name LIKE '%晶';

SELECT name,age,gender
FROM student
WHERE name LIKE '李%';

SELECT name,age,gender
FROM student
WHERE name LIKE '%江%';

SELECT name,age,gender
FROM student
WHERE name LIKE '%郭';

SELECT name,age,gender,job
FROM student
WHERE age BETWEEN 9 AND 12
   AND job LIKE '%课代表%';

SELECT name,age,gender
FROM student
WHERE name LIKE '_苗%';

SELECT name,age,gender,job
FROM student
WHERE name LIKE '邱%'
AND job LIKE '%课代表%';

SELECT name,age,gender,job
FROM student
WHERE job LIKE '%课代表%';

SELECT id,name,floor
FROM class
WHERE name LIKE '%2班%';

SELECT name,age,gender,comm
FROM teacher
WHERE comm=NULL ;   #永远不成立

SELECT name,age,gender,comm
FROM teacher
WHERE comm IS NULL ;

SELECT name,age,gender,comm
FROM teacher
WHERE comm<>NULL;   #永远不成立

SELECT name,age,gender,comm
FROM teacher
WHERE comm IS NOT NULL ;

SELECT name,salary
FROM teacher
ORDER BY salary;

SELECT name,salary
FROM teacher
ORDER BY salary DESC;

SELECT name,salary,comm
FROM teacher
ORDER BY comm;

SELECT name,age,birth
FROM student
ORDER BY birth;

SELECT name,age,birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth ;

SELECT name,comm,salary
FROM teacher
ORDER BY comm ASC,salary DESC;
#LIMIT M,N
#M跳过几条
#N M+1条开始看N条
SELECT name,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5;

SELECT name,comm
FROM teacher
ORDER BY comm DESC
LIMIT 12,3;

SELECT name,age,birth
FROM student
ORDER BY birth
LIMIT 10,5;

SELECT name,salary,salary*12
FROM teacher;

SELECT name,salary,comm,salary+comm
FROM teacher;

SELECT name,salary,comm,salary+IFNULL(comm,0)
FROM teacher;

SELECT name,IFNULL(comm,0),IFNULL(comm*12,0)
FROM teacher;

SELECT name,salary
FROM teacher
WHERE salary*12>60000
ORDER BY salary DESC;

SELECT name,comm
FROM teacher
WHERE comm<3000;

SELECT name,comm
FROM teacher
WHERE IFNULL(comm,0)<3000;

#列别名,下列句子等价
SELECT name,salary,salary*12 annusal
FROM teacher;
SELECT name,salary,salary*12 AS annusal
FROM teacher;
SELECT name,salary,salary*12 AS 'annusal'
FROM teacher;
SELECT name,salary,salary*12 AS "annusal"
FROM teacher;
SELECT name,salary,salary*12 'annusal'
FROM teacher;
SELECT name,salary,salary*12 "annusal"
FROM teacher;

SELECT name,salary,salary*12 'ann usal'
FROM teacher;

SELECT name,salary,salary*12 'from'
FROM teacher;

SELECT name,age,birth
FROM student
WHERE age=10
ORDER BY birth;

SELECT name,age
FROM student
WHERE age=8 AND name LIKE '%苗%';

SELECT name,age,subject_id,salary
FROM teacher
WHERE subject_id IN (1,2) AND salary>6000;

SELECT name,age,job
FROM student
WHERE age>10 AND job IN ('语文课代表','数学课代表');

SELECT name,subject_id,salary
FROM teacher
WHERE NOT subject_id=1
ORDER BY salary DESC;

SELECT name,comm
FROM teacher
WHERE IFNULL(comm,0)=0;

SELECT name,comm
FROM teacher
ORDER BY comm DESC;

SELECT DISTINCT subject_id
FROM teacher
WHERE salary>8000;

SELECT id,name,age,birth
FROM student
ORDER BY birth DESC,id
LIMIT 5,5;

SELECT AVG(salary)
FROM teacher;

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM teacher;

SELECT AVG(salary)
FROM teacher
WHERE subject_id=1;

SELECT name
FROM teacher;

SELECT COUNT(name)
FROM teacher;

SELECT COUNT(comm)
FROM teacher;#20个老师17个奖金

SELECT AVG(comm)
FROM teacher;#3041.1765

SELECT SUM(comm)
FROM teacher;#51700

SELECT IFNULL(comm,0)
FROM teacher;
SELECT AVG(IFNULL(comm,0))
FROM teacher;
SELECT AVG(IFNULL(comm,0)) '平均奖金'
FROM teacher;

SELECT COUNT(comm)
FROM teacher;#17
SELECT COUNT(*)
FROM teacher;
SELECT COUNT(1)
FROM teacher;
SELECT COUNT(id)
FROM teacher;

SELECT AVG(IFNULL(comm,0)),SUM(comm)
FROM teacher;

SELECT COUNT(*)
FROM teacher
WHERE subject_id=2;

SELECT COUNT(*)
FROM student
WHERE class_id=1;

SELECT MIN(birth)
FROM student;

SELECT COUNT(*)
FROM student
WHERE age=11 AND job LIKE '%课代表%';

SELECT COUNT(*)
FROM student
WHERE name LIKE '张%';

SELECT MIN(salary)
FROM teacher
WHERE salary>5000;

SELECT COUNT(*)
FROM class
WHERE floor=4;

SELECT AVG(IFNULL(salary,0))
FROM teacher
WHERE title='总监';

SELECT AVG(salary),title
FROM teacher
GROUP BY title;

SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;

SELECT  COUNT(*) '人数',
        MIN(birth) '最大生日',
        MAX(birth) '最小生日',
        job
FROM student
GROUP BY job;

SELECT COUNT(*),class_id,gender
FROM student
GROUP BY class_id,gender;

SELECT COUNT(*),class_id,job
FROM student
GROUP BY class_id,job;

SELECT AVG(salary) '平均工资',subject_id
FROM teacher
GROUP BY subject_id
ORDER BY '平均工资' DESC;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary)>6000;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary)>9000;

SELECT SUM(salary) '工资总和',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(comm)>4000;

SELECT AVG(salary)
FROM teacher
WHERE gender='男'
GROUP BY subject_id
HAVING MIN(salary)>4000;

SELECT COUNT(*),class_id
FROM student
WHERE class_id<6
GROUP BY class_id;

SELECT COUNT(*)
FROM class
WHERE floor=3;

SELECT AVG(salary)
FROM teacher
WHERE salary<8000;

SELECT MIN(birth) '年纪最大',
       class_id
FROM student
GROUP BY class_id
HAVING COUNT(*)>60;
6:查看班级人数超过60人的班级中年纪最大的同学生日是多少?
SELECT MIN(birth)
FROM student
WHERE class_id IN (
    SELECT class_id
    FROM student
    GROUP BY class_id
    HAVING COUNT(*)>60
);
SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;#每个班有多少人

SELECT AVG(age)
FROM teacher
WHERE subject_id=1;

SELECT MIN(age),subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(age)>35;

SELECT AVG(salary),title
FROM teacher
GROUP BY title
HAVING COUNT(*)>4;


SELECT SUM(salary) '工资总和',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(IFNULL(comm,0))>4000;

SELECT AVG(salary),subject_id
FROM teacher
WHERE gender='男'
GROUP BY subject_id
HAVING MIN(salary)>4000;

SELECT COUNT(*),class_id
FROM student
WHERE class_id<6
GROUP BY class_id;

SELECT COUNT(*)
FROM class
WHERE floor=3;

SELECT AVG(salary)
FROM teacher
WHERE salary<8000;

SELECT MIN(birth) '年纪最大',
       class_id
FROM student
GROUP BY class_id
HAVING COUNT(*)>60;

SELECT AVG(age)
FROM teacher
WHERE subject_id=1;

SELECT MIN(age),subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(age)>35;

SELECT AVG(salary),title
FROM teacher
GROUP BY title
HAVING COUNT(*)>4;

SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;#每个班有多少人

#比王克晶工资高的老师都有谁？
SELECT salary
FROM teacher
WHERE name='王克晶';
SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary
              FROM teacher
              WHERE name='王克晶');

SELECT AVG(salary)
FROM teacher;
SELECT salary
FROM teacher
WHERE salary>(SELECT AVG(salary)
              FROM teacher);

SELECT name
FROM student
WHERE class_id=(SELECT class_id
                FROM student
                WHERE name='李费水');

SELECT name
FROM class
WHERE floor=(SELECT floor
             FROM class
             WHERE name='3年级2班');

SELECT name,salary,comm
FROM teacher
WHERE salary=(SELECT MAX(salary)
              FROM teacher);
SELECT name,salary,comm
FROM teacher
GROUP BY salary DESC
LIMIT 0,1;

SELECT name
FROM student
WHERE class_id=(SELECT id
                FROM class
                WHERE name='3年级2班');

SELECT name
FROM teacher
WHERE subject_id=(SELECT id
                  FROM subject
                  WHERE name='语文');

SELECT name,class_id
FROM student
WHERE class_id IN (SELECT class_id
                   FROM student
                   WHERE name IN ('祝雷','李费水'));

SELECT name
FROM teacher
WHERE subject_id NOT IN (SELECT subject_id
                         FROM teacher
                         WHERE name IN ('范传奇','王克晶'));

SELECT name,salary
FROM teacher
WHERE salary>ALL(SELECT salary
                 FROM teacher
                 WHERE subject_id IN (2,4));

SELECT name,salary,subject_id
FROM teacher;
UPDATE teacher
SET salary=salary-500
WHERE subject_id=(SELECT subject_id
                  FROM teacher
                  WHERE name='范传奇');

UPDATE teacher
SET salary=(SELECT salary
            FROM teacher
            WHERE name='范传奇')
WHERE name='王克晶';
UPDATE teacher
SET salary=8000
WHERE name='王克晶';

BEGIN ;#开启事务
#删除与范传奇教同一科目的所有老师
#DELETE FROM teacher
#WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='范传奇');
SELECT * FROM teacher;
ROLLBACK ;#回滚,BEGIN到ROLLBACK之间的所有操作全部撤销
COMMIT ;#提交，BEGIN到COMMIT之间的所有操作全部执行

SELECT MAX(salary) max_sal,
       MIN(salary) min_sal,
       AVG(salary) avg_sal,
       SUM(salary) sum_sal,
       subject_id
FROM teacher
GROUP BY subject_id;

CREATE TABLE t_teacher_salary_info
AS
SELECT MAX(salary) max_sal,
       MIN(salary) min_sal,
       AVG(salary) avg_sal,
       SUM(salary) sum_sal,
       subject_id
FROM teacher
GROUP BY subject_id;
SHOW TABLES;
SELECT * FROM t_teacher_salary_info;
#查看负责课程编号1的老师最高工资是多少
SELECT max_sal
FROM t_teacher_salary_info
WHERE subject_id=1;

CREATE VIEW v_teacher_info
AS
SELECT id,name,title,gender,age,subject_id
FROM teacher;

SELECT  * FROM v_teacher_info;

SELECT teacher.name,subject.name
FROM teacher,subject
WHERE teacher.subject_id=subject.id;

SELECT class.name,teacher.name
FROM class,teacher
WHERE teacher.id=class.teacher_id;
SELECT name,id
FROM teacher;
SELECT name,teacher_id
FROM class;

SELECT c.name,t.name
FROM class c,teacher t
WHERE t.id=c.teacher_id;

SELECT s.name,s.age,c.name,c.floor
FROM class c,student s
WHERE c.id=s.class_id;

SELECT s.name,s.gender,s.age,l.name
FROM student s,location l
WHERE l.id=s.location_id;
SELECT name,location_id
FROM student
WHERE location_id=0;
SELECT name,id
FROM location;

SELECT c.name,t.name
FROM class c,teacher t
WHERE t.id=c.teacher_id
AND t.name='王克晶';

SELECT l.name,s.name,s.age,s.gender
FROM location l,student s
WHERE l.id=s.location_id
AND l.name='南京';

SELECT c.name,c.floor,t.name,t.salary
FROM teacher t,class c
WHERE t.id=c.teacher_id
AND c.name LIKE '3年级%';

SELECT s.name,s.age,s.gender,s.job,c.name,c.floor
FROM student s,class c
WHERE s.class_id=c.id
AND c.name LIKE '5年级%'
AND s.job='中队长';
SELECT *
FROM class;

SELECT s.name,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.name='范传奇';

SELECT s.name,l.name,c.name
FROM student s,location l,class c
WHERE s.location_id=l.id AND s.class_id=c.id
AND c.name='1年级1班';

SELECT l.name,s.name
FROM location l,student s
WHERE l.id=s.location_id
AND l.name='北京';

SELECT t.name,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
AND s.name='英语';

SELECT s.name,c.name,t.name
FROM student s,teacher t,class c
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.name='刘苍松';

SELECT t.name,s.name,c.name
FROM teacher t,subject s,class c
WHERE t.subject_id=s.id AND c.teacher_id=t.id
AND s.name='语文';

SELECT DISTINCT l.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.name='王克晶';

SELECT t.name,s.name,c.name
FROM teacher t,class c,subject s
WHERE t.id=c.teacher_id AND t.subject_id=s.id
AND c.name LIKE '3年级%';

SELECT s.name,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.salary>10000
AND s.job='大队长';
SELECT name,salary
FROM teacher;

SELECT su.name,t.name,s.name,c.name
FROM teacher t,class c,student s,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
AND s.name='李费水';

SELECT t.name,s.name,c.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND c.floor=4
AND s.job IN ('大队长','中队长');

SELECT s.name,c.name,t.name,s.birth
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND s.birth=(SELECT MAX(birth)
             FROM student);

SELECT su.name,t.name,c.name,s.name,l.name
FROM student s,class c,teacher t,subject su,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.subject_id=su.id AND s.location_id=l.id
AND su.name='语文';

SELECT s.name,c.name,t.name,l.name
FROM student s,class c,teacher t,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id AND s.location_id=l.id
AND s.birth=(SELECT MAX(birth)
             FROM student);

SELECT s.name,c.name,t.name,l.name
FROM student s,class c,teacher t,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary)
              FROM teacher)
AND l.name='北京';

SELECT t.name,s.name,c.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.name='范传奇';
SELECT COUNT(*)
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND t.name='范传奇';

SELECT AVG(t.salary) '平均工资'
FROM teacher t,subject s
WHERE t.subject_id=s.id
AND s.name='语文';

SELECT t.name,t.salary,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id;
SELECT AVG(t.salary),s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
GROUP BY s.name;

SELECT AVG(t.salary),s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
GROUP BY s.name
HAVING AVG(t.salary)>6000;

SELECT l.name,t.name,s.name,c.name
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary) FROM teacher);
SELECT l.name,COUNT(*) '人数'
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name
HAVING COUNT(*)>3
ORDER BY 人数 DESC;

1:先将待统计的数据查询出来-工资最高的老师班里的学生和城市信息
1.1:未知条件?最高工资是多少
SELECT MAX(salary) FROM teacher
    1.2:数据都来自哪些表?
    teacher t,class c,student s,location l
1.3:连接条件?
    t.id=c.teacher_id , c.id=s.class_id , l.id=s.location_id
1.4:过滤条件?
    t.salary=最高工资(1.1子查询)
SELECT t.name,c.name,s.name,l.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id
  AND t.salary=(SELECT MAX(salary) FROM teacher);

2:分组
每个城市的学生各多少人
GROUP BY l.name

3:过滤分组?(仅查看超过三个人的城市)该分组记录数超过3条
HAVING COUNT(*)>3

4:添加聚合函数进行分组统计
SELECT COUNT(*),l.name,t.name,c.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name
HAVING COUNT(*)>3;

5:排序,人数从多到少
SELECT COUNT(*) '人数',l.name,t.name,c.name                              #5
FROM teacher t,class c,student s,location l                             #1
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id      #2
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name                                           #3
HAVING COUNT(*)>3                                                       #4
ORDER BY 人数 DESC;














SELECT COUNT(*)
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.name=(
    SELECT name
    FROM teacher
    HAVING MAX(salary)
    );
SELECT COUNT(*)
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND t.salary=(
    SELECT MAX(salary)
    FROM teacher
);#对
SELECT *
FROM teacher;

1:教语文的老师所带班级各多少学生?
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
AND su.name='语文'
GROUP BY t.name;

2:每门课的老师所带班级各多少学生?
SELECT su.name,COUNT(*)
FROM teacher t,student s,class c,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
GROUP BY su.name;

3:来自上海的学生的班主任都有谁?
SELECT DISTINCT t.name
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND l.name='上海';

4:来自南京的学生共多少人
SELECT COUNT(*)
FROM student s,location l
WHERE l.id=s.location_id
AND l.name='南京';

5:来自武汉的男同学和女同学分别多少人?
SELECT COUNT(*),s.gender
FROM student s,location l
WHERE l.id=s.location_id
AND l.name='武汉'
GROUP BY s.gender;

6:每个城市的学生各多少人
SELECT COUNT(*),l.name
FROM student s,location l
WHERE l.id=s.location_id
GROUP BY l.name;

7:高于平均工资的老师所带的班级分别多少学生?
SELECT t.name,COUNT(*),c.name
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.salary>(
    SELECT AVG(salary)
    FROM teacher
)
GROUP BY t.name;

8:每个老师班里各多少来自郑州的学生?
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND l.name='郑州'
GROUP BY t.name;

SELECT s.name,su.name,tss.score
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND su.name='语文';

SELECT s.name,su.name,tss.score
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND s.name='李费水';

SELECT *
FROM t_stu_subject_score;
DESC t_stu_subject_score;

SELECT s.name,c.name,tss.score,su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name='1年级1班' AND su.name='语文';

SELECT COUNT(*),c.name,AVG(tss.score),su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name='1年级1班' AND su.name='数学';

SELECT COUNT(*),AVG(tss.score),su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name LIKE '6年级%' AND su.name='英语';

SELECT AVG(tss.score)
FROM student s,class c,t_stu_subject_score tss,subject su,teacher t
WHERE t.id=c.teacher_id AND c.id=s.class_id
    AND s.id=tss.stu_id AND tss.subject_id=su.id
AND t.name='刘苍松' AND su.name='英语';

SELECT AVG(tss.score),MAX(tss.score),MIN(tss.score),su.name
FROM teacher t,class c,student s,t_stu_subject_score tss,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND s.id=tss.stu_id AND su.id=tss.subject_id
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY su.name;

SELECT AVG(tss.score),s.name
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND s.job='大队长'
GROUP BY s.name;

SELECT s.name,c.name,s.job
FROM class c,student s
WHERE c.id=s.class_id
AND c.name='1年级1班';
#上下两个表一样
SELECT s.name,c.name,s.job
FROM class c
JOIN student s ON c.id=s.class_id
WHERE c.name='1年级1班';

SELECT s.name,c.name,t.name
FROM teacher t
JOIN class c ON t.id=c.teacher_id
JOIN student s ON c.id=s.class_id
WHERE t.name='王克晶' AND s.gender='女';

SELECT t.name,su.name
FROM teacher t
JOIN subject su ON t.subject_id=su.id
WHERE su.name='英语';

SELECT c.name,t.name
FROM class c
JOIN teacher t ON t.id=c.teacher_id;

SELECT t.name,c.name,s.name,l.name,tss.score,su.name
FROM teacher t
JOIN class c ON t.id=c.teacher_id
JOIN student s ON c.id=s.class_id
JOIN location l ON l.id=s.location_id
JOIN t_stu_subject_score tss ON tss.stu_id=s.id
JOIN subject su ON su.id=tss.subject_id
WHERE t.name='刘苍松' AND l.name='南京' AND su.name='语文';

SELECT t.name,c.name,s.name,l.name,AVG(tss.score),su.name
FROM teacher t
         JOIN class c ON t.id=c.teacher_id
         JOIN student s ON c.id=s.class_id
         JOIN location l ON l.id=s.location_id
         JOIN t_stu_subject_score tss ON tss.stu_id=s.id
         JOIN subject su ON su.id=tss.subject_id
WHERE t.name='刘苍松' AND su.name='语文'
GROUP BY c.name,l.name;

SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON t.id=c.teacher_id;
#上下两表一样
SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT OUTER JOIN teacher t ON t.id=c.teacher_id;

SELECT c.name,c.floor,t.name,t.salary
FROM class c
RIGHT JOIN teacher t ON t.id=c.teacher_id;


SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON t.id=c.teacher_id
UNION
SELECT c.name,c.floor,t.name,t.salary
FROM class c
RIGHT JOIN teacher t ON t.id=c.teacher_id;

SELECT COUNT(*) '人数','姓张' AS '姓氏'
FROM student
WHERE  name LIKE '张%'
UNION
SELECT COUNT(*) '人数','姓李' AS '姓氏'
FROM student
WHERE  name LIKE '李%'
UNION
SELECT COUNT(*) '人数','姓王' AS '姓氏'
FROM student
WHERE  name LIKE '王%';

SELECT *
FROM teacher;
SELECT t.name,m.name
FROM teacher t
JOIN teacher m ON t.manager=m.id;

SELECT s.name '学生',l.name '班长'
FROM student s
JOIN student l ON s.team_leader=l.id
WHERE s.name='祝雷';

SELECT s.name '学生',l.name '班长',s.birth
FROM student s
JOIN student l ON s.team_leader=l.id
WHERE s.birth=(SELECT MIN(birth)FROM student);

SELECT t.name,m.name,c.name
FROM class c
JOIN teacher t ON t.id=c.teacher_id
JOIN teacher m ON t.manager=m.id
WHERE c.name='3年级2班';

SELECT s.name'学生',c.name'班级',t.name'老师',m.name'上司'
FROM student s
JOIN class c ON s.class_id=c.id
JOIN teacher t ON t.id=c.teacher_id
JOIN teacher m ON t.manager=m.id
WHERE s.birth=(SELECT MIN(birth)FROM student);

SHOW DATABASES ;
USE tedu;
SHOW TABLES ;
DESC userinfo;
SELECT *
FROM userinfo;



创建库 egdb1 并指定字符集为UTF8
CREATE DATABASE egdb1 CHARSET=UTF8;
查看所有的库
SHOW DATABASES ;
切换到库 egdb1
USE egdb1;
在该库中创建数据表 books 并指定字符集为UTF8，字段类型和约束要求如下：
书籍编号id：整型，设置为主键并设置自增长属性
书籍名称bname：变长，宽度为50，不能为NULL
书籍作者author：变长，宽度为30，不能为NULL，设置默认值为 '佚名'
书籍出版社press：变长，宽度为128
书籍价格price：浮点型，总位数为7，小数位为2，不能为NULL，设置默认值为 0.00
书籍描述comment：text类型，不用指定宽度
CREATE TABLE books(
    id INT PRIMARY KEY AUTO_INCREMENT,
    bname VARCHAR(50) NOT NULL,
    author VARCHAR(30) NOT NULL DEFAULT '佚名',
    press VARCHAR(128),
    price DOUBLE(7,2) NOT NULL DEFAULT 0.00,
    comment TEXT
);
查看表结构
DESC books;
SHOW TABLES ;
在表中任意插入1条表记录；
INSERT INTO books (bname)
    VALUES ('Java从入门到精通');
查询所有表记录；
SELECT * FROM books;
在表中批量插入表记录
INSERT INTO books(bname,author,press,price,comment)
VALUES
    ('边城','沈从文','机械工业出版社',36,'小城故事多'),
    ('骆驼祥子','老舍','机械工业出版社',43,'你是祥子么'),
    ('茶馆','老舍','中国文学出版社',55,'老北京'),
    ('呐喊','鲁迅','人民教育出版社',71,'最后的声音'),
    ('朝花夕拾','鲁迅','中国文学出版社',53,'好时光'),
    ('围城','钱钟书','中国文学出版社',44,'你心中的围城是什么');

INSERT INTO books(bname,author,press,price)
VALUES
    ('林家铺子','茅盾','机械工业出版社',51),
    ('子夜','茅盾','人民教育出版社',47);

SQL语句练习

查找人民教育出版社出版的图书
SELECT *
FROM books
WHERE press='人民教育出版社';
查找老舍写的，中国文学出版社出版的图书
SELECT *
FROM books
WHERE author='老舍' AND press='中国文学出版社';
查找价格超过60元[不包括60元]的图书，只看书名和价格
SELECT bname,price
FROM books
WHERE price>60;
将呐喊的价格修改为45元
UPDATE books
SET price=45
WHERE bname='呐喊';
增加一个字段出版时间，类型为 date ，放在价格字段price后面
ALTER TABLE books
ADD date DATE AFTER price;#date可以保存年月日
修改所有老舍的作品出版时间为 2018-10-01
UPDATE books
SET date='2018-10-01'
WHERE author='老舍';
所有鲁迅的图书价格增加5元
UPDATE books
SET price=price+5
WHERE author='鲁迅';
删除所有价格超过70元[包含70元]或者不到40元[不包含40元]的图书
DELETE FROM books
WHERE price>=70 OR price<40;
SELECT * FROM books;

测试
1011test

-- 1. 创建库 **egdb2**，指定字符集为UTF8
CREATE DATABASE IF NOT EXISTS egdb2 CHARSET = UTF8;

-- 2. 切换库
USE egdb2;

-- 3. 创建表 **sanguo** 并指定字符集为UTF8，字段要求如下
--    - 英雄编号id：INT，主键自增长
--    - 英雄名称name：变长，字符宽度为30，不允许为空
--    - 性别gender：定长，字符宽度为1，不允许为空
--    - 国家country：定长，字符宽度为1，不允许为空
--    - 攻击力attack：INT，不允许为空
--    - 防御力defense：INT，不允许为空
CREATE TABLE IF NOT EXISTS sanguo
(
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(30) NOT NULL,
    gender  CHAR(1)     NOT NULL,
    country CHAR(1)     NOT NULL,
    attack  INT         NOT NULL,
    defense INT         NOT NULL
) CHARSET = UTF8;
DESC sanguo;
-- 4.在表中插入数据
INSERT INTO sanguo
VALUES (1, '曹操', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);
SELECT * FROM sanguo;

-- 1. 查找所有蜀国人信息，按照攻击力排名
SELECT name,country,attack FROM sanguo
WHERE country='蜀'
ORDER BY attack DESC;
-- 2. 查找攻击力超过200[**包含200**]的魏国英雄名字和攻击力并显示为姓名， 攻击力
SELECT name,attack,country FROM sanguo
WHERE attack>=200 AND country='魏';
-- 3. 所有英雄按照攻击力降序排序，如果相同则按照防御升序排序
SELECT name,attack,defense FROM sanguo
ORDER BY attack DESC,defense ASC;
-- 4. 查找名字为3个字符的数据
SELECT name FROM sanguo
WHERE name LIKE '___';
-- 5. 找到魏国防御力排名2-3名的英雄
SELECT name,country,defense FROM sanguo
WHERE country='魏'
ORDER BY defense DESC
LIMIT 1,2;
-- 6. 找出表中的最大攻击力的值？
SELECT MAX(attack) max_attack FROM sanguo;
-- 7. 表中共有多少个英雄？
SELECT COUNT(*) FROM sanguo;
-- 8. 蜀国英雄中攻击值大于200[**包含200**]的英雄的数量
SELECT COUNT(*) FROM sanguo
WHERE country='蜀' AND attack>=200;
-- 9. 计算每个国家的平均攻击力
SELECT country,AVG(attack) FROM sanguo
GROUP BY country;
-- 10. 统计每个国家男性英雄和女性英雄的平均攻击力
-- 魏  男  xxx
-- 魏  女  xxx
-- 蜀  男  xxx
-- 蜀  女  xxx
SELECT country,gender,AVG(attack) FROM sanguo
GROUP BY country, gender;
-- 11. 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量
SELECT country,COUNT(*) number FROM sanguo
WHERE gender='男'
GROUP BY country
ORDER BY number DESC
LIMIT 0,2;

-- 12. 统计平均攻击力大于250[**包含250**]的国家的英雄数量
SELECT country,COUNT(*) number,AVG(attack) FROM sanguo
GROUP BY country
HAVING AVG(attack)>=250;


SHOW DATABASES;
SHOW TABLES;
CREATE DATABASE mybatisdb;
CREATE TABLE customers(
    cust_id VARCHAR(32) PRIMARY KEY AUTO_INCREMENT,
    cust_tel VARCHAR(11),
    cust_name VARCHAR(64),
    cityy VARCHAR(128)
);
CREATE TABLE orders(
    order_id VARCHAR(32) PRIMARY KEY AUTO_INCREMENT,
    cust_id VARCHAR(32),
    order_date DATE,
    ststua INT,
    products_num INT,
    amt DOUBLE(10,2)
);
INSERT INTO customers(cust_id,cust_tel,cust_name,cityy)
VALUES ('0004','13811112222','Tony','成都');
#{}
INSERT INTO orders(order_id,cust_id,order_date,ststua,products_num,amt)
VALUES ('202301020004','0004',new Date(),3,4,800.88);

SELECT (customers.cust_name,city)
FROM customers
WHERE cust_id=#{};
SELECT (cust_name,city)
FROM customers
WHERE c.city=#{};
SELECT (c.cust_id,c.cust_name,o.order_id,o.amt,o.ststua)
FROM customers c,orders o
WHERE c.cust_id=#{};
 */